SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vw_ordenDeMeritoTTP
AS
SELECT DISTINCT 
               CO.Descripcion AS Convocatoria, CO.idConvocatoria, N .NivelEnsenanza AS Nivel, IC.Nivel AS IdNivel, IC.Modalidad AS Modalidad, 
               CL.Descripcion AS Ciclo, IC.Ciclo AS IdCiclo, IC.NroDeOrden, L.localidad, L.idLocalidad, CG.idCargoGenerico AS IdCargo, CG.Descripcion AS Cargo, 
               AG.idAsignaturaGenerica AS IdAsignatura, AG.Descripcion AS Asignatura, C.Descripcion AS CategoriaDeTitulo, A.Documento, 
               A.Nombres AS NombreYApellido, ic.puntaje, A.Domicilio AS Direccion, L2.localidad AS ALocalidad, A.Telefono AS TE, CASE WHEN N 
               .IdNivelEnsenanza IN (2) THEN A.LegajoDeInicial WHEN N .IdNivelEnsenanza IN (3) THEN A.LegajoDePrimaria WHEN N .IdNivelEnsenanza IN (4) 
               THEN A.LegajoDeMedia END AS Legajo, IC.PromedioTitulo AS Promedio, IC.TituloEspecifico, IC.SituacionRevista, CASE WHEN N 
               .IdNivelEnsenanza IN (2) THEN 1 WHEN N .IdNivelEnsenanza IN (4) THEN 2 WHEN N .IdNivelEnsenanza = 3 AND CL.IdCiclo = 1 THEN 1 WHEN N 
               .IdNivelEnsenanza = 3 AND CL.IdCiclo = 2 THEN 2 ELSE 3 END TituloDelReporte, CASE WHEN ic.cargogenerico IN (35, 320, 405, 620) AND 
               ic.titulovalorado = 71870 THEN '##' WHEN ic.cargogenerico IN (55, 415, 330) AND ic.titulovalorado = 46480 THEN '**' ELSE '  ' END AS tit
FROM  dbo.tb_InscripcionesConvocatorias IC INNER JOIN
               dbo.tb_Localidades L ON IC.Localidad = L.idLocalidad INNER JOIN
               dbo.tb_CargosGenericos CG ON CG.idCargoGenerico = IC.CargoGenerico INNER JOIN
               dbo.tb_AsignaturasGenericas AG ON AG.idAsignaturaGenerica = IC.AsignaturaGenerica INNER JOIN
               dbo.tb_Agentes A ON A.idAgente = IC.Agente INNER JOIN
               dbo.tb_Convocatorias CO ON CO.idConvocatoria = IC.Convocatoria INNER JOIN
               dbo.tb_NivelesEnsenanza N ON N .idNivelEnsenanza = IC.Nivel INNER JOIN
               dbo.tb_Ciclos CL ON CL.IdCiclo = IC.Ciclo LEFT OUTER JOIN
               dbo.tb_CategoriasDeTitulo C ON C.idCategoria = IC.CategoriaDeTitulo LEFT OUTER JOIN
               dbo.tb_Localidades L2 ON L2.idLocalidad = A.Localidad INNER JOIN
               dbo.tb_Parametros ON IC.Convocatoria = CAST(dbo.tb_Parametros.Valor1 AS int) AND IC.TituloEspecifico = dbo.tb_Parametros.Valor2
WHERE (dbo.tb_Parametros.idParametro = 65) AND (IC.Modalidad = 7)
GO
GRANT SELECT ON  [dbo].[vw_ordenDeMeritoTTP] TO [SoloVer]
GO
